package com.rongji.cms.tools.temp.db;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.io.FileUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.File;
import java.io.IOException;
import java.util.*;


/**
 * @author one.xu
 * @version v1.0
 * @description
 * @date 2022/1/5 17:59
 * <p>
 * 这段代码目前存在问题：
 * 目前是以数据库db1为基础进行对比，这样如果db2有某张表而db1没有,就对比不出来；
 * 必须是2个不同的mysql，db1和db2不能在同一个mysql里
 */
public class DbComparedTest {
    private static JdbcTemplate db1;
    private static JdbcTemplate db2;
    static String db1Name = "test1";
//    static String db2Name = "test2";

    static {
        db1 = new JdbcTemplate();
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://192.168.212.224:3306/" + db1Name + "?serverTimezone=GMT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&nullCatalogMeansCurrent=true");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        db1.setDataSource(dataSource);


        db2 = new JdbcTemplate();
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource2.setUrl("jdbc:mysql://192.168.212.224:3346/" + db1Name + "?serverTimezone=GMT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&nullCatalogMeansCurrent=true");
        dataSource2.setUsername("root");
        dataSource2.setPassword("ruoyi123");
        db2.setDataSource(dataSource2);
    }

    //移除AUTO_INCREMENT
    private static String handTable(String table) {
        return table.replaceAll("AUTO_INCREMENT=\\d+", "").trim();

    }

    //文本对比,高亮显示
    public static String getcompareStr(String char1, String char2) {
        String bcolor = "<span style='background-color:yellow;color:red;'>";
        String ecolor = "</span>";
        StringBuffer sb = new StringBuffer();
        char[] a = new char[char1.length()];
        for (int i = 0; i < char1.length(); i++) {
            a[i] = char1.charAt(i);
        }
        char[] b = new char[char2.length()];
        for (int i = 0; i < char2.length(); i++) {
            b[i] = char2.charAt(i);
        }
        // 不同字符集合
        Map<Object, Object> map1 = new HashMap<>();
        // 包含字符集合
        Map<Object, Object> map2 = new HashMap<>();
        for (int i = 0; i < a.length; i++) {
            if (i == a.length - 1) {
                if (i > 1) {
                    if (String.valueOf(b).contains(String.valueOf(a[i - 1]) + String.valueOf(a[i]))) {
                        map2.put(i - 1, a[i - 1]);
                        map2.put(i, a[i]);
                    } else {
                        map1.put(i, a[i]);
                    }
                } else {
                    map2.put(i, a[i]);
                }
            } else {
                if (String.valueOf(b).contains(String.valueOf(a[i]) + String.valueOf(a[i + 1]))) {
                    if (i > 1) {
                        if (String.valueOf(b).contains(String.valueOf(a[i - 1]) + String.valueOf(a[i]))) {
                            map2.put(i - 1, a[i - 1]);
                            map2.put(i, a[i]);
                        }
                    } else {
                        map2.put(i, a[i]);
                    }
                } else {
                    if (i > 0) {
                        if (String.valueOf(b).contains(String.valueOf(a[i - 1]) + String.valueOf(a[i]))) {
                            map2.put(i - 1, a[i - 1]);
                            map2.put(i, a[i]);
                        } else {
                            map1.put(i, a[i]);
                        }
                    } else {
                        map1.put(i, a[i]);
                    }
                }
            }
        }
        for (int i = 0; i < a.length; i++) {
            if (map1.get(i) != null) {
                sb.append(bcolor).append(map1.get(i)).append(ecolor);
            } else if (map2.get(i) != null) {
                sb.append(map2.get(i));
            }
        }
        return sb.toString();
    }

    public static void main(String[] args) throws IOException {
        //需要比对的数据库名,多个逗号连接
        String dbs = db1Name;
//        String dbs = db1Name + "," + db2Name;
        String tableSql = "select table_name,table_comment FROM information_schema.tables WHERE table_schema=?";
        //忽略比对的表名,多个逗号连接  支持正则
        String ignoreTable = "|(msg_log_\\w+)|(hand_log_\\w+)";
        String tableCreateSql = " show create table ";
        List<Map<String, Object>> tableDiffList = new ArrayList<>();
        Map<String, Object> diffMap;
        Map<String, Integer> dbTableCountMap = new HashMap<>();
        int dbTableCount = 0;
        for (String db : dbs.split(",")) {
            dbTableCount = 0;
            List<Map<String, Object>> tables = db1.queryForList(tableSql, db);
            for (int j = 0; j < tables.size(); j++) {
                String table = tables.get(j).get("table_name").toString();
                if (table.matches(ignoreTable)) {
                    continue;
                }
                diffMap = new HashMap<>();
                Map<String, Object> sourceTableMap = db1.queryForMap(tableCreateSql + db + "." + table);
                String sourceTable = handTable(MapUtils.getString(sourceTableMap, "Create Table"));
                diffMap.put("dbName", db);
                diffMap.put("sourceTable", sourceTable);
                Map<String, Object> targetTableMap;
                try {
                    targetTableMap = db2.queryForMap(tableCreateSql + db + "." + table);
                } catch (Exception e) {
                    dbTableCount = dbTableCount + 1;
                    diffMap.put("targetTable", "不存在");
                    tableDiffList.add(diffMap);
                    continue;
                }
                String targetTable = handTable(MapUtils.getString(targetTableMap, "Create Table"));
                if (sourceTable.equals(targetTable)) {
                    continue;
                }
                diffMap.put("targetTable", getcompareStr(targetTable, sourceTable));
                diffMap.put("sourceTable", getcompareStr(sourceTable, targetTable));
                tableDiffList.add(diffMap);
                dbTableCount = dbTableCount + 1;

            }
            dbTableCountMap.put(db, dbTableCount);
        }
        StringJoiner html = new StringJoiner("\n");
        html.add("<table style=\"font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #729ea5;border-collapse: collapse;\" border=\"1\">\n" +
                "<tr><th >数据库名</th><th>源表</th><th>对比表</th></tr>");

        for (Map<String, Object> m : tableDiffList) {
            String dbName = m.get("dbName").toString();
            Integer dbTabeCount = dbTableCountMap.get(dbName);
            if (dbTabeCount != null) {
                html.add("<tr> <td rowspan=\"" + dbTabeCount + "\">" + dbName + "</td>");
                dbTableCountMap.remove(dbName);
            }
            html.add("<td>\n" +
                    "<pre> " + m.get("sourceTable").toString() + " <pre>\n" +
                    "</td>");
            html.add("<td>\n" +
                    "<pre> " + m.get("targetTable").toString() + " <pre>\n" +
                    "</td>");
            html.add("</tr>");
        }
        html.add("</table>");
        FileUtils.writeStringToFile(new File("d:\\DbComparedTest.html"), html.toString());
        System.out.println("对比完毕，请查看d:\\DbComparedTest.html");

    }
}

